package br.gov.cemaden.pcd;

import br.gov.cemaden.pcd.file.CSVFileWriter;
import br.gov.cemaden.pcd.util.HibernateUtil;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.type.DoubleType;
import org.hibernate.type.LongType;
import org.hibernate.type.StringType;

/**
 * Hello world!
 *
 */
public class PCDDatabackup 
{
    public static void main( String[] args )
    {
        
        String path = null;
        Integer givenMonth = null;
        Integer givenYear = null;
        int year;
        int month;
        
        Calendar calendar = Calendar.getInstance();
        calendar.add(Calendar.DATE, -1);
        int currentDay = calendar.get(Calendar.DAY_OF_MONTH);
        int currentMonth = calendar.get(Calendar.MONTH);
        int currentYear = calendar.get(Calendar.YEAR);
        
        if(args.length < 1){
            System.out.println("You must give the parameters");
            System.exit(0);
        }
        else if(args.length > 0){
            if(Files.exists(Paths.get(args[0]))){
                path = args[0];
                System.out.println(path);
            }
            else{
                System.err.println("You must give a valid path");
                System.exit(0);
            }        
            if(args[1] != null && args[2] != null){
                
                givenMonth = Integer.parseInt(args[1]);
                givenYear = Integer.parseInt(args[2]);
                
                if(givenMonth < 1 || givenMonth > 12)
                    givenMonth = null;
                if(givenYear.toString().length() != 4 || givenYear > currentYear)
                    givenYear = null;
                if(givenYear.equals(currentYear)){
                    if(givenMonth > currentMonth)
                        givenMonth = null;
                }
            }
        }                
        
        month = currentMonth;
        year = currentYear;                
        
        String from = null;
        String to = null;
        
        if(givenMonth != null && givenYear != null){
            if((givenMonth.equals(currentMonth)) && (givenYear.equals(currentYear))){
                from = String.format("%d-%d-01 00:00:00", currentYear, currentMonth);
                to = String.format("%d-%d-%d 23:59:59", currentYear, currentMonth, currentDay);
            }
            else{                
                Calendar cal = new GregorianCalendar(givenYear, givenMonth, 1);
                int days = cal.getActualMaximum(Calendar.DAY_OF_MONTH);
                
                from = String.format("%d-%d-01 00:00:00", givenYear, givenMonth);
                to = String.format("%d-%d-%d 23:59:59", givenYear, givenMonth, days);
                
                year = givenYear;
                month = givenMonth;
            }
        }
        else{
            from = String.format("%d-%d-01 00:00:00", currentYear, currentMonth);
            to = String.format("%d-%d-%d 23:59:59", currentYear, currentMonth, currentDay);
        }
        
        Session session;
        session = HibernateUtil.getSessionFactory().openSession();
        
        if (session != null) {

            Transaction tx = null;
            List list1 = null;
            List list2 = null;
            List list3 = null;
            List list4 = null;            
            
            try {
                tx = session.beginTransaction();
                
                list1 = session.createSQLQuery("SELECT DISTINCT m.uf FROM ger_municipio AS m").list();                
                session.flush();
                tx.commit();
                
            } catch(Throwable ex){
                if(tx != null)
                    tx.rollback();
                System.err.println(ex);
            }
            
            for(Iterator i = list1.iterator(); i.hasNext();){

                List<String[]> data = new LinkedList<String[]>();
                
                String uf = (String) i.next();

                /*try {
                    tx = session.beginTransaction();
                    
                    String query = "SELECT DISTINCT m.id_municipio"
                            + " FROM pcds.pcds_estacao e INNER JOIN ger_municipio m USING (id_municipio)" 
                            + " WHERE e.status = 0 AND e.id_rede = 11 AND m.uf = :uf"
                            + " ORDER BY 1";

                    list2 = session.createSQLQuery(query)
                            .addScalar("id_municipio", LongType.INSTANCE)
                            .setParameter("uf", uf)
                            .list();
                    session.flush();
                    tx.commit();
                    
                } catch (Throwable ex) {
                    if (tx != null)
                        tx.rollback();
                    System.err.println(ex);
                } */                   

                //for (Iterator j = list2.iterator(); j.hasNext();) {

                    //Long idMunicipio = (Long) j.next();

                    try {
                        tx = session.beginTransaction();

                        String subquery = "SELECT DISTINCT m.id_municipio"
                            + " FROM pcds.pcds_estacao e INNER JOIN ger_municipio m USING (id_municipio)" 
                            + " WHERE e.status = 0 AND e.id_rede = 11 AND m.uf = :uf"
                            + " ORDER BY 1";
                        
                        String query = "SELECT e.codestacao, e.nome, e.latitude, e.longitude, e.id_estacao" 
                                + " FROM pcds.pcds_estacao e" 
                                + " WHERE e.id_municipio IN ("+subquery+")"
                                + " AND e.id_rede = 11" 
                                + " AND e.status = 0";
                        
                        list3 = session.createSQLQuery(query)
                                .addScalar("codestacao", StringType.INSTANCE)
                                .addScalar("nome", StringType.INSTANCE)
                                .addScalar("latitude", DoubleType.INSTANCE)
                                .addScalar("longitude", DoubleType.INSTANCE)
                                .addScalar("id_estacao", LongType.INSTANCE)
                                .setParameter("uf", uf)
                                .list();                                
                        session.flush();
                        tx.commit();
                        
                    } catch (Throwable ex) {
                        if (tx != null)
                            tx.rollback();
                        System.err.println(ex);
                    }

                    for (Iterator k = list3.iterator(); k.hasNext();) {

                        Object[] c = (Object[]) k.next();

                        Long idEstacao = (Long) c[4];
                        String codEstacao = (String) c[0];
                        String nomeEstacao = (String) c[1];
                        Double longitude = (Double) c[2];
                        Double latitude = (Double) c[3];

                        try {
                            tx = session.beginTransaction();
                            
                            String sql = "SELECT a.id_aquisicao, a.datahora, m.valor" 
                                    + " FROM pcds.pcds_aquisicao a" 
                                    + " INNER JOIN pcds.pcds_medida m ON a.id_aquisicao = m.id_aquisicao AND m.id_sensor = 10" 
                                    + " WHERE a.datahora BETWEEN '"+from+"' and '"+to+"'" 
                                    + " AND a.id_estacao = :id_estacao";

                            list4 = session.createSQLQuery(sql)
                                    .setParameter("id_estacao", idEstacao)
                                    .list();
                            session.flush();
                            tx.commit();
                            
                        } catch (Throwable ex) {
                            if (tx != null)
                                tx.rollback();
                            System.err.println(ex);
                        }

                        for (Iterator l = list4.iterator(); l.hasNext();) {

                            Object[] d = (Object[]) l.next();

                            //String line = String.format("%s;%s;%s;%s;%s;%s%n", codEstacao, nomeEstacao, latitude, longitude, d[1].toString(), d[2].toString());
                            String[] line = {codEstacao, nomeEstacao, latitude.toString(), longitude.toString(), d[1].toString(), d[2].toString()};

                            data.add(line);                        
                        }
                    }                
                //}
                
                String filename = String.format("%s/%s_%d_%d.csv", path, uf, year, month);
                Path file = Paths.get(filename);
                new CSVFileWriter().write(file, data, ';');       
            }            
            session.close();
        }
        HibernateUtil.getSessionFactory().close();
    }
}
